﻿Imports System.Data.SqlClient
Public Class CDBConnection
    Public Property sqlConnection As SqlConnection

    Private sqlCommand As New SqlCommand
    Private sqlTransaction As SqlTransaction
    Private isInTransaction As Boolean
    Public Sub New()
        Dim connectionString As String
        connectionString = "Server=127.0.0.1;" _
            & "database= MzrSoft ;" _
            & "uid=sa;" _
            & "pwd=wfaizjj;"
        sqlConnection = New SqlConnection(connectionString)
        Try
            sqlConnection.Open()
            sqlCommand.Connection = sqlConnection
        Catch ex As Exception
            sqlConnection = Nothing

            MsgBox("连接数据库失败!")
        End Try
    End Sub

    Public Sub New(ByVal server As String, ByVal database As String, ByVal uid As String, ByVal pwd As String)
        Dim connectionString As String
        connectionString = "Server=" & server & ";" _
            & "database=" & database & ";" _
            & "uid=" & uid & ";" _
            & "pwd=" & pwd & ";"
        sqlConnection = New SqlConnection(connectionString)
        Try
            sqlConnection.Open()
            sqlCommand.Connection = sqlConnection
        Catch ex As Exception
            sqlConnection = Nothing
            MsgBox("连接数据库失败!")
        End Try
    End Sub

    Public Function ExecuteSql(ByVal sql As String)
        sqlCommand.CommandText = sql
        If isInTransaction Then
            sqlCommand.Transaction = sqlTransaction
        End If
        Try
            Dim result As Integer = sqlCommand.ExecuteNonQuery()
            If result >= -1 Then
                Return True
            Else
                Return False
            End If
        Catch ex As Exception
            MsgBox("sql执行失败," & ex.Message)
            Return False
        End Try
    End Function

    Public Function ExecuteScalarSql(ByVal sql As String) As Object
        sqlCommand.CommandText = sql
        If isInTransaction Then
            sqlCommand.Transaction = sqlTransaction
        End If
        Try
            Return sqlCommand.ExecuteScalar()
        Catch ex As Exception
            MsgBox("sql执行失败", MsgBoxStyle.Exclamation, "系统提示")
            Return Nothing
        End Try
    End Function

    Public Sub BeginTrans()
        Try
            sqlTransaction = sqlConnection.BeginTransaction()
            isInTransaction = True
        Catch ex As Exception
            Console.WriteLine(ex.Message)
            isInTransaction = False
        End Try
    End Sub

    Public Sub CommitTrans()
        If Not isInTransaction Then
            MsgBox("无法执行事务，不是在事务中")
            Exit Sub
        End If
        Try
            sqlTransaction.Commit()
            isInTransaction = False
            sqlTransaction.Dispose()
        Catch ex As Exception
            Console.WriteLine("Commit Exception Type: {0}", ex.GetType())
            Console.WriteLine("  Message: {0}", ex.Message)
            Try
                sqlTransaction.Rollback()
            Catch ex2 As Exception
                Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType())
                Console.WriteLine("  Message: {0}", ex2.Message)
            End Try
        End Try
    End Sub

    Public Sub RollbackTrans()
        If Not isInTransaction Then
            MsgBox("无法回滚，不是在事务中")
            Exit Sub
        End If

        Try
            sqlTransaction.Rollback()
            isInTransaction = False
            sqlTransaction.Dispose()
        Catch ex As Exception
            Console.WriteLine("Rollback Exception Type: {0}", ex.GetType())
            Console.WriteLine("  Message: {0}", ex.Message)
        End Try

    End Sub

    Public Function GetDataSet(ByVal sql As String) As DataSet
        Dim dataSet As New DataSet
        Dim dataAdapter As SqlDataAdapter
        Try
            dataAdapter = New SqlDataAdapter(sql, sqlConnection)
            dataAdapter.Fill(dataSet)
        Catch ex As Exception
            Console.WriteLine("读取数据失败，" & ex.Message)
            Return Nothing
        End Try
        Return dataSet
    End Function

    Public Function GetDataSet(ByVal sql As String, ByRef dataAdapter As SqlDataAdapter) As DataSet
        Dim dataSet As New DataSet
        Try
            dataAdapter = New SqlDataAdapter(sql, sqlConnection)
            Dim builder As New SqlCommandBuilder(dataAdapter)
            dataAdapter.Fill(dataSet)
            builder = Nothing
        Catch ex As Exception
            Console.WriteLine("读取数据失败，" & ex.Message)
            Return Nothing
        End Try
        Return dataSet
    End Function

    Public Function GetDataTable(ByVal sql As String) As DataTable
        Dim dataTable As New DataTable
        Dim dataAdapter As SqlDataAdapter
        Try
            dataAdapter = New SqlDataAdapter(sql, sqlConnection)
            dataAdapter.Fill(dataTable)
            Return dataTable
        Catch ex As Exception
            Console.WriteLine("读取数据失败，" & ex.Message)
            Return Nothing
        End Try
    End Function

    Public Function GetIntCalculateValue(ByVal sql As String) As Integer
        Try
            Dim dt1 As DataTable
            dt1 = GetDataTable(sql)
            Return CInt(dt1.Rows(0).Item(0))
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation, "系统提示")
            Return Nothing
        End Try
    End Function

    Public Function GetCurrentDatetime() As Date
        Dim sql As String
        Dim dt1 As DataTable
        sql = " Select getDate()"
        dt1 = GetDataTable(sql)
        Return CDate(dt1.Rows(0).Item(0))
    End Function

    Public Function GetCurrentDate() As Date
        Return DateValue(GetCurrentDatetime)
    End Function

    Public Function CloseConnection() As Boolean
        Try
            sqlConnection.Close()
        Catch ex As Exception
            MsgBox("连接关闭失败，" & ex.Message)
            Return False
        End Try
        Return True
    End Function


    Public Shared Function GetSqlDbType(ByVal dataType As String) As SqlDbType
        Dim sqlDbType As SqlDbType
        For Each str1 As String In [Enum].GetNames(sqlDbType.GetType)
            If str1.ToLower = dataType.ToLower Then
                Return CType([Enum].Parse(sqlDbType.GetType, str1), SqlDbType)
            End If
        Next
        Return sqlDbType.NVarChar
    End Function

    Public Shared Function SqlType2Type(ByVal dataType As String) As Type
        Return SqlType2Type(GetSqlDbType(dataType))
    End Function


    Public Shared Function SqlType2Type(ByVal sqlType As SqlDbType) As Type
        Select Case (sqlType)
            Case SqlDbType.BigInt
                Return (New System.Int64).GetType
            Case SqlDbType.Binary
                Return (New Object).GetType
            Case SqlDbType.Bit
                Return (New Boolean).GetType
            Case SqlDbType.Char
                Return "".GetType
            Case SqlDbType.DateTime
                Return (New DateTime).GetType
            Case SqlDbType.Decimal
                Return (New Decimal).GetType
            Case SqlDbType.Float
                Return (New Double).GetType
            Case SqlDbType.Image
                Return (New Object).GetType
            Case SqlDbType.Int
                Return (New System.Int32).GetType
            Case SqlDbType.Money
                Return (New Decimal).GetType
            Case SqlDbType.NChar
                Return "".GetType
            Case SqlDbType.NText
                Return "".GetType
            Case SqlDbType.NVarChar
                Return "".GetType
            Case SqlDbType.Real
                Return (New Single).GetType
            Case SqlDbType.SmallDateTime
                Return (New DateTime).GetType
            Case SqlDbType.Date
                Return (New DateTime).GetType
            Case SqlDbType.SmallInt
                Return (New Int16).GetType
            Case SqlDbType.SmallMoney
                Return (New Decimal).GetType
            Case SqlDbType.Text
                Return "".GetType
            Case SqlDbType.Timestamp
                Return (New Object).GetType
            Case SqlDbType.TinyInt
                Return (New Byte).GetType
            Case SqlDbType.Udt
                Return (New Object).GetType
            Case SqlDbType.UniqueIdentifier
                Return (New Object).GetType
            Case SqlDbType.VarBinary
                Return (New Object).GetType
            Case SqlDbType.VarChar
                Return "".GetType
            Case SqlDbType.Variant
                Return (New Object).GetType
            Case SqlDbType.Xml
                Return (New Object).GetType
            Case Else
                Return Nothing
        End Select
    End Function
End Class
